<?php

/**
 * naf::db::Select is (kind of simple, as alwayse ;) ) Naf implementation of M. Fowler's Query-Object design pattern.
 * 
 * $Id: Select.php 265 2008-09-16 13:09:31Z vbolshov $
 */

namespace km\db;

use \IteratorAggregate;
use \Countable;
use \PDO;
use \Km;
use \LogicException;
use km\util\Pager;

class Select implements IteratorAggregate, Countable {
	/**
	 * column set mode
	 */
	const MODE_APPEND = 0,
		MODE_REPLACE = 1;
	/**
	 * @var array
	 */
	private $errors = array();
	/**
	 * @var PDO
	 */
	private static $default_connection;
	/**
	 * @var PDO
	 */
	private $connection;
	/**
	 * @var array
	 */
	protected $fetch_mode = array(PDO::FETCH_ASSOC);
	/**
	 * FROM clause spec
	 *
	 * @var string
	 */
	private $from;
	/**
	 * @var string[]
	 */
	private $cols = array(), 
		$join = array(), 
		$where = array(), 
		$order = array(), 
		$group = array(), 
		$having = array();
	/**
	 * @var array
	 */
	private $bind = array();
	
	/**
	 * @var int
	 */
	private $limit, $offset;
	/**
	 * SQL expression to use whe performing COUNT() query
	 * @var string
	 */
	private $countExpression = '*';
	
	function __construct($from = null, $cols = '*')
	{
		if ($from)
		{
			$this->from = $from;
			$this->cols = (array) $cols;
		}
	}
	/**
	 * Specify FROM clause
	 *
	 * @param string $from
	 * @return Select
	 */
	function from($from)
	{
		$this->from = $from;
		return $this;
	}
	/**
	 * Setup columns to select
	 *
	 * @param string $cols either a string or an array of strings - the SQL expressions
	 * @param int $mode Select::MODE_APPEND or Select::MODE_REPLACE
	 * @return Select
	 */
	function cols($cols, $mode = self::MODE_APPEND)
	{
		if (self::MODE_APPEND == $mode)
		{
			$this->cols = array_merge($this->cols, (array) $cols);
		} elseif (self::MODE_REPLACE == $mode) {
			$this->cols = (array) $cols;
		} else {
			throw new InvalidArgumentException('Invalid mode');
		}
		
		return $this;
	}
	/**
	 * @param string | string[] $cols
	 * @return Select
	 */
	function setCols($cols)
	{
		$this->cols = (array) $cols;
		return $this;
	}
	/**
	 * @param string | string[] $cols
	 * @return Select
	 */
	function appendCols($cols)
	{
		$this->cols = array_merge($this->cols, (array) $cols);
		return $this;
	}
	/**
	 * @param string
	 * @return Select
	 */
	function setCountExpression($expression)
	{
		$this->countExpression = $expression;
		return $this;
	}
	/**
	 * @param PDO $c
	 */
	static function setDefaultConnection($c)
	{
		self::$default_connection = $c;
	}
	/**
	 * @param PDO $c
	 * @return Select
	 */
	function setConnection($c)
	{
		$this->connection = $c;
		return $this;
	}
	/**
	 * @param int $mode
	 * @param mixed $opt1, $opt2,..
	 * @return Select
	 */
	function setFetchMode($mode, $opts = null)
	{
		$this->fetch_mode = is_array($mode) ?
			$mode :
			func_get_args();
		return $this;
	}
	
	/**
	 * paginate result set.
	 * @param int $limit
	 * @param int $offset
	 * @return Select
	 */
	function paginate($page_number, $page_size)
	{
		$this->offset = ($page_number - 1) * $page_size;
		$this->limit = $page_size;
		return $this;
	}
	/**
	 * apply LIMIT ... OFFSET
	 * @param int $limit
	 * @param int $offset
	 * @return Select
	 */
	function limit($limit, $offset = 0)
	{
		$this->limit = $limit;
		$this->offset = $offset;
		return $this;
	}
	/**
	 * Apply LIMIT/OFFSET from pager
	 *
	 * @param Pager $p
	 * @return Select $this
	 */
	function limitByPager(Pager $p)
	{
		$this->paginate($p->getPageNumber(), $p->getPageSize());
		return $this;
	}
	/**
	 * Add a JOIN clause
	 * @param string $sql
	 * @param string | array $cols
	 * @param array $bind
	 * @return Select
	 */
	function join($sql, $cols = null, $bind = array())
	{
		$this->bind($sql, $bind);
		$this->join[] = $sql;
		$this->bind = array_merge($this->bind, $bind);
		$this->cols($cols);
		return $this;
	}
	/**
	 * Specify a WHERE condition
	 *
	 * @param string $sql
	 * @param mixed $bind1, $bind2,..
	 * @return Select
	 */
	function where($sql)
	{
		$bind_deployed = func_get_args();
		array_shift($bind_deployed);
		$this->bind($sql, $bind_deployed);
		$this->where[] = $sql;
		$this->bind = array_merge($this->bind, $bind_deployed);
		return $this;
	}
	/**
	 * Specify a WHERE condition that will apply only if $condition evaluates to TRUE
	 *
	 * @param bool $condition
	 * @param string $sql
	 * @param mixed $bind1, $bind2,..
	 * @return Select
	 */
	function ifWhere($condition, $sql)
	{
		if ($condition) {
			$args = func_get_args();
			array_shift($args);
			call_user_func_array(array($this, 'where'), $args);
		}
		return $this;
	}
	/**
	 * Specify a GROUP BY clause
	 *
	 * @param string | array $expression
	 * @return Select
	 */
	function group($expression)
	{
		$this->group = array_merge($this->group, (array) $expression);
		return $this;
	}
	/**
	 * Specify a HAVING clause
	 *
	 * @param string $expression
	 * @param mixed $bind1, $bind2,..
	 * @return Select
	 */
	function having($expression)
	{
		$bind_deployed = func_get_args();
		array_shift($bind_deployed);
		$this->bind($expression, $bind_deployed);
		$this->having[] = $expression;
		$this->bind = array_merge($this->bind, $bind_deployed);
		return $this;
	}
	/**
	 * Specify ORDER BY clause
	 *
	 * @param string | array $expression
	 * @return Select
	 */
	function order($expression)
	{
		$this->order = array_merge($this->order, (array) $expression);
		return $this;
	}
	/**
	 * Reset ORDER BY clause
	 * @return Select
	 */
	function resetOrder()
	{
		$this->order = array();
		return $this;
	}
	
	/**
	 * Perform the resulting SQL query
	 *
	 * @param int $fetch_mode
	 * @return PDOStatement
	 */
	function query($fetch_mode = null)
	{
		$s = $this->getConnection()->prepare($this->sql());
		$s->execute($this->bind);
		call_user_func_array(array($s, 'setFetchMode'), (null === $fetch_mode) ? $this->fetch_mode : (array) $fetch_mode);
		return $s;
	}
	/**
	 * Fetch the very first row
	 *
	 * @param int $fetch_mode
	 * @return ActiveRecord | mixed (depends on fetch_mode)
	 */
	function fetchOne($fetch_mode = null)
	{
		$limit = $this->limit;
		$offset = $this->offset;
		$ret = $this->limit(1)->query($fetch_mode)->fetch();
		$this->limit($limit, $offset);// restore state
		return $ret;
	}
	/**
	 * Fetch all rows as an array
	 * @param int $fetch_mode
	 */
	function fetchAll($fetch_mode = null)
	{
		return $this->query($fetch_mode)->fetchAll();
	}
	
	/**
	 * Execute COUNT($expression)
	 * 
	 * NOTE! When HAVING clauses are encountered, count() attempts to apply them in WHERE clause
	 *
	 * @param string $expression the SQL expression to be wrapped in COUNT()
	 * @return int
	 */
	function count($expression = null)
	{
		try {
			if (! $expression) {
				$expression = $this->countExpression;
			}
			$s = $this->getConnection()->prepare($this->sql("COUNT($expression)", true));
			$s->execute($this->bind);
			return (int) $s->fetchColumn();
		} catch (PDOException $e) {
			$this->errors[] = $e->getMessage();
			return 0;
		}
	}
	/**
	 * @return string[]
	 */
	function getErrors()
	{
		return $this->errors;
	}
	
	/**
	 * @implements IteratorAggregate
	 * @return Iterator
	 */
	function getIterator()
	{
		return $this->query();
	}
	/**
	 * @return string
	 */
	function __toString()
	{
		$sql = $this->sql();
		
		$explode = explode('?', $sql);
		$sql = array_shift($explode);
		foreach ($explode as $i => $part)
		{
			if (is_numeric($this->bind[$i]))
			{
				$sql .= $this->bind[$i];
			} elseif (is_string($this->bind[$i])) {
				$sql .= "'" . addcslashes($this->bind[$i], "'") . "'";
			} elseif (is_bool($this->bind[$i])) {
				$sql .= (int) $this->bind[$i];
			} elseif (is_null($this->bind[$i])) {
				$sql .= 'NULL';
			} else {
				throw new InvalidArgumentException("Unsupported binding type: " . gettype($this->bind[$i]));
			}
			
			$sql .= $part;
		}
		
		return $sql;
	}
	/**
	 *
	 * @param string $prefix
	 * @return string hash for this select object (to be used as a cache key)
	 */
	function hash($prefix = '')
	{
		$crc32 = (string) crc32($this->__toString());
		if ('-' == $crc32{0}) {
			$crc32 = '_' . substr($crc32, 1);
		}
		return $prefix . $crc32;
	}
	/**
	 * @param string $cols
	 * @param bool $count whether we should build a COUNT query
	 * @return string
	 */
	function sql($cols = null, $count = false)
	{
		// the basics
		if (null === $cols)
		{
			$cols = implode(', ', $this->cols);
		}
		
		$sql = 'SELECT ' . $cols . ' FROM ' . $this->from;
		if (count($this->join))
		{
			$sql .= ' ' . implode(' ', $this->join);
		}
		
		// WHERE
		if ($count && (count($this->where) || count($this->having)))
		{
			$sql .= ' WHERE (' . implode(') AND (', array_merge($this->where, $this->having)) . ')';
		} elseif (count($this->where)) {
			$sql .= ' WHERE (' . implode(') AND (', $this->where) . ')';
		}
		
		// GROUP BY
		if (count($this->group) && ! $count)
		{
			$sql .= ' GROUP BY ' . implode(', ', $this->group);
		}
		
		// HAVING
		if (count($this->having) && ! $count)
		{
			$sql .= ' HAVING (' . implode(', ', $this->having) . ')';
		}
		
		// ORDER BY
		if (count($this->order) && ! $count)
		{
			$sql .= ' ORDER BY ' . implode(', ', $this->order);
		}
		
		// LIMIT x OFFSET y
		if ($this->limit && ! $count)
		{
			$sql .= ' LIMIT ' . (int) $this->limit . ' OFFSET ' . (int) $this->offset;
		}
		
		return $sql;
	}
	/**
	 * generate "IS [NOT] NULL" SQL expression for $expression,
	 * f. e.:
	 * Select::sqlNull('id', true) returns "id IS NULL", whereas
	 * Select::sqlNull('id', false) returns "id IS NOT NULL"
	 * @param string $expression
	 * @param bool $is_null
	 */
	static function sqlNull($expression, $is_null)
	{
		return $expression . ' IS' . ($is_null ? '' : ' NOT') . ' NULL';
	}
	
	/**
	 * @return PDO
	 */
	function getConnection()
	{
		if ($this->connection)
		{
			return $this->connection;
		} elseif (self::$default_connection) {
			return self::$default_connection;
		} else {
			return Km::getDbConnection();
		}
	}
	
	/**
	 * @param string $sql
	 * @param array $bind
	 */
	private function bind(& $sql, & $bind)
	{
		$bind_deployed = array();
		foreach ($bind as $i => $value)
		{
			if (is_array($value))
			{
				if (! isset($sql_split))
				{
					$sql_split = explode('?', $sql);
				}
				
				$sql_split[$i] .= str_repeat('?,', count($value) - 1);
				$bind_deployed  = array_merge($bind_deployed, $value);
				
			} else {
				$bind_deployed[] = $value;
			}
		}
		
		if (isset($sql_split))
		{
			$sql = implode('?', $sql_split);
		}
		
		$bind = $bind_deployed;
	}
}